import os
import json
from datetime import datetime, timedelta
from sqlalchemy import create_engine, text
import pandas as pd
engine = create_engine(
   'mysql+mysqlconnector://root:Bz_202501@bj-cdb-ckq2r8ro.sql.tencentcdb.com:25622/bz_system',
    pool_recycle=3600,
    echo=False,  # 将echo从True改为False以关闭SQL日志
    isolation_level="READ COMMITTED",
    pool_pre_ping=True
)

root_path = '/mnt'
folder_list = ['sle_bioage','sleep_noon_to_noon','sleep_episodes','daily_summary','pwf_daily','hrv_daily','near_real_time','fitness_event','mental_daily', 'hrv_continuous', 'mental_hourly', 'pwf_continuous']
folder_date_flag = {'fitness_event': 'startDate','sle_bioage': 'endDate','sleep_noon_to_noon': 'startDate', 'daily_summary': 'startDate', 'hrv_daily': 'startDate','mental_daily': 'startDate', 'pwf_daily': 'startDate','near_real_time':'endDate','hrv_continuous':'startDate', 'mental_hourly':'startDate', 'pwf_continuous':'startDate'}

user_dict = {
    "David": "975783c2-ed1e-4d66-931a-db11e92415ba",
    "王龙": "e66935eb-faec-47d4-bfb9-c6584ecbde62",
    "郭晓鹏": "1e8733d0-6426-46e4-82e2-a54d2af426d1",
    #"毕秀静": "7a7670bb-7f04-46c5-bd97-dcae13231ccf",
    "张颖": "f6e3a514-75ce-46c5-898c-83478c965edb",
    "石雅俊": "ae12c612-d1a9-4154-b5cf-af5ac6000d27",
    "孙格格": "526135ee-aceb-4902-9c61-6ca921811ca6",
    "李洋": "cdebfcd6-3fef-4315-b960-3b27e208662f",
    "向先火": "06afd291-cfc4-4d2a-bc82-0de2e185a554",
    #"矦佳乐": "64375b0c-f59c-4ea5-ac71-acdc1f8ec70e",
    "于庚辰": "c281f805-bfb7-4645-a256-bd5a7bff106e",
    "大左": "6529e607-d8e1-41c2-8f49-bae0f78cb8ce"
}
result={}
date_list = [ '2025-06-09']

def gather_data_from_folder(root_path, profile_id, date_dir):

    pass





def main():
   
   #初始化结果表
    for user_name, profile_id in user_dict.items():
        result[user_name] = {}
        for date in date_list:
            result[user_name][date] = {
    'fitness_event':'No',
    'sle_bioage':'No',
    'sleep_noon_to_noon': 'No',
      'daily_summary': 'No', 
      'hrv_daily': 'No',
      'mental_daily': 'No',
        'pwf_daily': 'No',
      'near_real_time':'No',
      'hrv_continuous':'No', 
      'mental_hourly':'No', 
      'pwf_continuous':'No',
      'sleep_episodes':'No'
    }
    for user_name,profile_id in user_dict.items():
      ##对这个用户遍历所有的文件夹
      for folder_name in folder_list:
        ##先找到近3日每天最晚来的文件
        #print(f"正在处理用户 {user_name} ({folder_name}) 的数据...")
        folder_data = []
        for i in range(3):
            current_date = (datetime.now() - timedelta(days=i)).strftime('%Y-%m-%d')
            data_path = os.path.join(root_path, folder_name, current_date, profile_id)
            if not os.path.exists(data_path):
                continue

            for json_name in os.listdir(data_path):
                
                json_path = os.path.join(data_path, json_name)
                if os.path.getsize(json_path) == 0:
                   continue
                try:
                    with open(json_path, 'r') as f:
                    
                        data = json.load(f)
                        if folder_name == 'sleep_episodes':
                            date_flag1 = datetime.fromtimestamp(int(data['header']['startTime'])).strftime('%Y-%m-%d')
                            if date_flag1 in date_list:
                                result[user_name][date_flag1][folder_name] = 'Yes'
                        else:
                            date_flag1 = data['header'][folder_date_flag[folder_name]][0:10]
                            if date_flag1 in date_list:
                                result[user_name][date_flag1][folder_name] = 'Yes'
                except json.JSONDecodeError as e:
                    print(f"文件 {json_name} 解析错误: {str(e)}")
    #print(json.dumps(result, indent=2, ensure_ascii=False))
    rows = []
    for user_name, date_dict in result.items():
        for date, folder_dict in date_dict.items():
            row = {'user_name': user_name, 'date': date}
            row.update(folder_dict)
            rows.append(row)
    df = pd.DataFrame(rows)
    # 保存为CSV
    df.to_csv('/home/xiang/gather_data_from_folder/user_folder_stat.csv', index=False, encoding='utf-8-sig')
    print("已保存为 user_folder_stat.csv")
    print(df.head())



if __name__ == "__main__":
    main()